set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table bidefault.dwd_tab_barscan_arrival_send_base_dt
select
     base.waybill_no          as waybill_no              --运单号
    ,base.actual_send_time    as actual_send_time        --网点实际发件时间
    ,base.first_network_code  as first_network_code      --始发网点编码
    ,base.first_nodal_name    as first_nodal_name        --始发集散名称
    ,base.first_nodal_code    as first_nodal_code        --始发集散编码
    ,base.first_center_code   as first_center_code       --始发中心编码
    ,base.first_center_name   as first_center_name       --始发中心名称
    ,base.first_center_code   as actual_center_code      --实际交件中心编码
    ,base.first_center_name   as actual_center_name      --实际交件中心名称
    ,base.first_center_time   as actual_center_time      --实际交件中心扫描时间
    ,case when branch.arrival_center_arrival_time is not null
          then least(nvl(branch.arrival_center_arrival_time,'2099-12-31 00:00:00') --到车任务号
                    ,nvl(base.first_center_time            ,'2099-12-31 00:00:00'))
          when branch.send_center_arrival_time is not null
          then least(nvl(branch.send_center_arrival_time   ,'2099-12-31 00:00:00')--发车任务号
                    ,nvl(base.first_center_time            ,'2099-12-31 00:00:00'))
          else base.first_center_time end as actual_center_arr_time
    ,base.first_nodal_code   as actual_collect_code         --实际交件集散点编码
    ,base.first_nodal_name   as actual_collect_name         --实际交件集散点名称
    ,base.first_nodal_time   as plan_collect_time           --规划到达集散时间
    ,base.first_nodal_code   as plan_collect_code           --规划到达集散编码
    ,base.first_nodal_name   as plan_collect_name           --规划到达集散名称
    ,base.first_nodal_time   as actual_collect_time         --实际交件集散点时间
    ,coalesce(branch.arrival_nodal_arrival_time
             ,branch.send_nodal_arrival_time
             ,base.first_nodal_time
    ) as actual_collect_arr_time    --实际交件集散点时间（包含支线任务到达时间）兜底逻辑到件、发件下一站、卸车、装车下一站
    ,base.first_nodal_time  as first_nodal_time           --始发集散点时间
    ,base.first_center_time as first_center_time          --始发中心时间
    ,coalesce(branch.arrival_center_shipment_no
             ,branch.send_center_shipment_no
     ) as actual_center_shipment_no
    ,base.first_nodal_send_time    as first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
    ,base.first_nodal_arrival_time as first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
    ,base.first_center_send_time   as first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
    ,nvl(base.first_center_arrival_time,base.first_center_send_time) as first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
    ,coalesce(branch.arrival_center_shipment_shifts
             ,branch.send_center_shipment_shifts
     ) as actual_center_shipment_shifts --实际交件支线任务班次
    ,branch.send_network_shipment_no as actual_network_shipment_no
    ,coalesce(branch.arrival_nodal_shipment_no
             ,branch.send_nodal_shipment_no
     )  as actual_nodal_shipment_no
from (
    select
         a.waybill_no
        ,a.actual_send_time
        ,a.first_network_code
        ,a.first_nodal_time
        ,a.first_nodal_code
        ,a.first_nodal_name
        ,a.first_center_time
        ,a.first_center_code
        ,a.first_center_name
        ,b.first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
        ,b.first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
        ,b.first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
        ,b.first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
    from (
        select
             waybill_no
            ,max(actual_send_time  ) as actual_send_time
            ,max(first_network_code) as first_network_code
            ,max(first_nodal_time  ) as first_nodal_time
            ,max(first_nodal_code  ) as first_nodal_code
            ,max(first_nodal_name  ) as first_nodal_name
            ,max(first_center_time ) as first_center_time
            ,max(first_center_code ) as first_center_code
            ,max(first_center_name ) as first_center_name
        from (
            select
                  waybill_no
                 ,case when network_type = 6 and scan_type = 'send' then scan_time      end as actual_send_time       --网点实际发件时间/最早发件时间
                 ,case when network_type = 6 and scan_type = 'send' then scan_site_code end as first_network_code     --始发网点编码
                 ,case when network_type = 5  then scan_time      end as first_nodal_time  --实际交件集散点时间
                 ,case when network_type = 5  then scan_site_code end as first_nodal_code  --实际交件集散点编码
                 ,case when network_type = 5  then scan_site_name end as first_nodal_name  --实际交件集散点名称
                 ,case when network_type = 4  then scan_time      end as first_center_time --实际交件中心扫描时间
                 ,case when network_type = 4  then scan_site_code end as first_center_code --实际交件中心编码
                 ,case when network_type = 4  then scan_site_name end as first_center_name --实际交件中心名称
            from (
                 select
                     *,row_number() over(partition by waybill_no,network_type order by scan_time) as rn
                 from jms_tmp.dwd_tab_barscan_base_dt base
            ) a where a.rn = 1
        ) a group by waybill_no
    ) a
    left join (
        select
             waybill_no
            ,max(first_nodal_send_time    ) as first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
            ,max(first_nodal_arrival_time ) as first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
            ,max(first_center_send_time   ) as first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
            ,max(first_center_arrival_time) as first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
        from(
            select
                 waybill_no
                ,case when network_type = 5 and scan_type = 'send'    then scan_time end as first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
                ,case when network_type = 5 and scan_type = 'arrival' then scan_time end as first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
                ,case when network_type = 4 and scan_type = 'arrival' then scan_time end as first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
                ,first_center_send_time --始发转运中心发件扫描时间 (20221113新增)
            from jms_tmp.dwd_tab_barscan_base_dt
        ) a group by waybill_no
    ) b on a.waybill_no = b.waybill_no
) base
left join (
    select
         waybill_no as waybill_no              --运单号
        ,max(arrival_nodal_arrival_time    ) as arrival_nodal_arrival_time     --优先取到件扫描对应支线实际到达时间
        ,max(send_nodal_arrival_time       ) as send_nodal_arrival_time        --次取发件扫描对应支线实际到达时间
        ,max(arrival_center_arrival_time   ) as arrival_center_arrival_time    --优先取到件扫描对应支线实际到达时间
        ,max(send_center_arrival_time      ) as send_center_arrival_time       --次取发件扫描对应支线实际到达时间
        ,max(arrival_center_shipment_no    ) as arrival_center_shipment_no     --优先取到件扫描对应支线任务号(中心)
        ,max(send_center_shipment_no       ) as send_center_shipment_no        --次取发件扫描对应支线任务号(中心)
        ,max(arrival_center_shipment_shifts) as arrival_center_shipment_shifts --优先取到件扫描对应支线任务班次
        ,max(send_center_shipment_shifts   ) as send_center_shipment_shifts    --次取发件扫描对应支线任务班次
        ,max(send_network_shipment_no)       as send_network_shipment_no       --网点直接取发件表任务单
        ,max(arrival_nodal_shipment_no)      as arrival_nodal_shipment_no      --优先取到件扫描对应支线任务号(集散)
        ,max(send_nodal_shipment_no)         as send_nodal_shipment_no         --次取发件扫描对应支线任务号(集散)
    from (
         select
               base.waybill_no     --运单号
              ,base.scan_time      --扫描时间
              ,base.input_time     --录入时间
              ,base.scan_site_code --扫描网点编码
              ,base.scan_site_name --扫描网点名称
              ,base.scan_type      --扫描类型
              ,case when base.network_type      = 5 and base.scan_type = 'arrival' then stop.actual_arrival_time end as arrival_nodal_arrival_time  --优先取到件扫描对应支线实际到达时间
              ,case when base.next_network_type = 5 and base.scan_type = 'send'    then stop.actual_arrival_time end as send_nodal_arrival_time     --次取发件扫描对应支线实际到达时间
              ,case when base.network_type      = 4 and base.scan_type = 'arrival' then stop.actual_arrival_time end as arrival_center_arrival_time --优先取到件扫描对应支线实际到达时间
              ,case when base.next_network_type = 4 and base.scan_type = 'send'    then stop.actual_arrival_time end as send_center_arrival_time    --次取发件扫描对应支线实际到达时间
              ,case when base.network_type      = 4 and base.scan_type = 'arrival' then stop.shipment_no end as arrival_center_shipment_no          --优先取到件扫描对应支线任务号
              ,case when base.next_network_type = 4 and base.scan_type = 'send'    then stop.shipment_no end as send_center_shipment_no             --次取发件扫描对应支线任务号
              ,case when base.network_type      = 4 and base.scan_type = 'arrival' then stop.shipment_shifts end as arrival_center_shipment_shifts  --优先取到件扫描对应支线任务班次
              ,case when base.next_network_type = 4 and base.scan_type = 'send'    then stop.shipment_shifts end as send_center_shipment_shifts     --次取发件扫描对应支线任务班次
              ,case when base.network_type      = 6 and base.scan_type = 'send'    then base.transfer_code end as send_network_shipment_no          --网点直接取发件表任务单
              ,case when base.network_type      = 5 and base.scan_type = 'arrival' then stop.shipment_no end as arrival_nodal_shipment_no           --优先取到件扫描对应支线任务号
              ,case when base.next_network_type = 5 and base.scan_type = 'send'    then stop.shipment_no end as send_nodal_shipment_no              --次取发件扫描对应支线任务号
         from (
             select
                  waybill_no
                 ,scan_time
                 ,input_time
                 ,scan_site_code
                 ,scan_site_name
                 ,next_station_code
                 ,transfer_code
                 ,network_type
                 ,next_network_type
                 ,scan_type
                 ,max(center_code) over(partition by waybill_no) as next_pre_site_code
             from (
                 select
                      *
                     ,case when network_type =  4 then scan_site_code end as center_code
                     ,row_number() over(partition by waybill_no,network_type order by scan_time) as rn
                 from jms_tmp.dwd_tab_barscan_base_dt base
                 where scan_type = 'arrival'
                 union all
                 select
                      *
                     ,case when network_type =  4 then scan_site_code end as center_code
                     ,row_number() over(partition by waybill_no,next_network_type order by scan_time desc) as rn
                 from jms_tmp.dwd_tab_barscan_base_dt base
                 where scan_type = 'send'
                 and !(scan_type = 'send' and network_type = 4)
             ) a where a.rn = 1
         ) base
         left join (
             select
                  ship_stop.shipment_no
                 ,ship_stop.sort_num
                 ,ship_stop.network_code        --经停网点编码
                 ,ship_stop.network_name        --经停网点名称
                 ,ship_stop.actual_arrival_time --最晚到车时间
                 ,ship_stop.pre_network_code    --经停网点编码
                 ,ship_stop.pre_network_name    --经停网点名称
                 ,ship.shipment_shifts          --任务班次 20230420新增
             from (
                 select
                      a.shipment_no         as shipment_no
                     ,a.sort_num            as sort_num
                     ,a.network_code        as network_code        --经停网点编码
                     ,a.network_name        as network_name        --经停网点名称
                     ,a.actual_arrival_time as actual_arrival_time --最晚到车时间
                     ,b.network_code        as pre_network_code    --经停网点编码
                     ,b.network_name        as pre_network_name    --经停网点名称
                 from (
                     select *
                     from (
                         select
                              shipment_no --任务号
                             ,sort_num
                             ,network_code --经停网点编码
                             ,network_name --经停网点名称
                             ,actual_arrival_time --最晚到车时间
                             ,row_number() over(partition by shipment_no,sort_num order by update_time desc) as rn
                         from jms_ods.yl_tmsnew_branch_shipment_stop_hi  --支线经停表
                         where substr(dt,1,10) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1) and dt <= concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
                           and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1)
                     ) a where a.rn = 1
                 ) a
                 left join (
                     select *
                     from (
                         select
                              shipment_no --任务号
                             ,sort_num
                             ,network_code --经停网点编码
                             ,network_name --经停网点名称
                             ,actual_arrival_time --最晚到车时间
                             ,row_number() over(partition by shipment_no,sort_num order by update_time desc) as rn
                         from jms_ods.yl_tmsnew_branch_shipment_stop_hi  --支线经停表
                         where substr(dt,1,10) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1) and dt <= concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
                           and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1)
                     ) a where a.rn = 1
                 ) b on a.shipment_no = b.shipment_no
                   and a.sort_num -1 = b.sort_num
                 union all
                 select
                      a.shipment_no         as shipment_no
                     ,a.sort_num            as sort_num
                     ,a.network_code        as network_code        --经停网点编码
                     ,a.network_name        as network_name        --经停网点名称
                     ,a.actual_arrival_time as actual_arrival_time --最晚到车时间
                     ,b.network_code        as pre_network_code    --经停网点编码
                     ,b.network_name        as pre_network_name    --经停网点名称
                 from (
                     select *
                     from (
                         select
                              shipment_no --任务号
                             ,sort_num
                             ,network_code --经停网点编码
                             ,network_name --经停网点名称
                             ,actual_arrival_time --最晚到车时间
                             ,row_number() over(partition by shipment_no,sort_num order by update_time desc) as rn
                         from jms_ods.yl_tmsnew_tms_shipment_stop_hf  --支线经停表
                         where dt = concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
                           and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1)
                     ) a where a.rn = 1
                 ) a
                 left join (
                     select *
                     from (
                         select
                              shipment_no --任务号
                             ,sort_num
                             ,network_code --经停网点编码
                             ,network_name --经停网点名称
                             ,actual_arrival_time --最晚到车时间
                             ,row_number() over(partition by shipment_no,sort_num order by update_time desc) as rn
                         from jms_ods.yl_tmsnew_tms_shipment_stop_hf  --支线经停表
                         where dt = concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
                           and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1)
                     ) a where a.rn = 1
                 ) b on a.shipment_no = b.shipment_no
                   and a.sort_num -1 = b.sort_num
             ) ship_stop
             left join (
                 select
                      shipment_no
                     ,shipment_shifts
                 from (
                     select
                          shipment_no
                         ,concat(shifts,'班') as shipment_shifts
                         ,row_number() over(partition by shipment_no order by update_time desc) as rn
                     from jms_ods.yl_tmsnew_branch_shipment_hi
                     where substr(dt,1,10) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1) and dt <= concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
                       and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1)
                 ) a where a.rn = 1
                 union all
                 select
                      shipment_no
                     ,concat(shifts,'班') as shipment_shifts
                 from jms_ods.yl_tmsnew_tms_shipment_hf
                 where dt = concat(date_add('{{ execution_date | cst_ds }}',1),'-06')
                  and to_date(create_time) between date_sub('{{ execution_date | cst_ds }}',20) and date_add('{{ execution_date | cst_ds }}',1)
             ) ship on ship_stop.shipment_no = ship.shipment_no
        ) stop on stop.shipment_no = nvl(base.transfer_code,base.waybill_no)     --优先取到件任务号的最晚到车时间
              and stop.network_code = case when base.scan_type in ('send') and base.next_network_type <> 4 then base.next_station_code --发件，如果不是发往中心就正常用下一站取
                                           when base.scan_type in ('send') then base.next_pre_site_code --发件下一站，实际到件中心
                                           when base.scan_type in ('arrival') and stop.pre_network_code = base.next_station_code then base.scan_site_code end --到件，任务号的上一站，与实际上一站一致才取到件任务号
    ) a group by waybill_no
) branch on base.waybill_no = branch.waybill_no;



----------------------------第二段：拿出T-2天揽收、入仓数据，取出运单，始发，实际，规划，维度数据
insert overwrite table bidefault.dwd_tab_barscan_wdie_base_dt
select
      wide.waybill_no              as waybill_no              --运单号
     ,wide.order_source            as order_source            --订单来源名称
     ,wide.goods_type_code         as goods_type_code         --物品类型编码
     ,wide.goods_type_name         as goods_type_name         --物品类型名称
     ,wide.last_network_code       as last_network_code       --运单表目的网点编码
     ,wide.last_network_name       as last_network_name       --运单表目的网点名称
     ,wide.early_scan_time         as early_scan_time         --最早扫描时间
     ,wide.early_scan_site_code    as early_scan_site_code    --最早的收件网点
     ,base.first_network_code      as first_network_code      --始发网点编码
     ,case when base.first_nodal_time < nvl(base.first_center_time,'2099-12-31 00:00:00') then base.first_nodal_code end as first_nodal_code        --始发集散编码
     ,case when base.first_nodal_time < nvl(base.first_center_time,'2099-12-31 00:00:00') then base.first_nodal_name end as first_nodal_name        --始发集散名称
     ,base.first_center_code       as first_center_code       --始发中心编码
     ,base.first_center_name       as first_center_name       --始发中心名称
     ,base.actual_center_code      as actual_center_code      --实际交件中心编码
     ,base.actual_center_name      as actual_center_name      --实际交件中心名称
     ,base.collect_proxy_code      as collect_proxy_code      --交件代理区编码
     ,base.collect_proxy           as collect_proxy           --交件代理区名称
     ,base.actual_send_time        as actual_send_time        --网点实际发件时间
     ,case when base.actual_collect_time < nvl(base.actual_center_time,'2099-12-31 00:00:00') then base.actual_collect_code end as actual_collect_code --实际交件集散点编码
     ,case when base.actual_collect_time < nvl(base.actual_center_time,'2099-12-31 00:00:00') then base.actual_collect_name end as actual_collect_name --实际交件集散点名称
     ,case when base.actual_collect_time < nvl(base.actual_center_time,'2099-12-31 00:00:00') then base.plan_collect_time   end as plan_collect_time   --规划到达集散时间
     ,case when base.actual_collect_time < nvl(base.actual_center_time,'2099-12-31 00:00:00') then base.plan_collect_code   end as plan_collect_code   --规划到达集散编码
     ,case when base.actual_collect_time < nvl(base.actual_center_time,'2099-12-31 00:00:00') then base.plan_collect_name   end as plan_collect_name   --规划到达集散名称
     ,case when base.actual_collect_arr_time < nvl(base.actual_center_arr_time,'2099-12-31 00:00:00') then base.actual_collect_arr_time end as actual_collect_arr_time --实际交件集散点时间
     ,case when base.actual_center_arr_time = '2099-12-31 00:00:00' then null else base.actual_center_arr_time end as actual_center_arr_time --实际首中心到车时间
     ,dim.city_id     as receive_city_id           --收件城市id
     ,dim.city_desc   as receive_city              --收件城市
     ,dim.manage_code as region_code               --收件管理大区编码
     ,dim.manage_name as region_name               --收件管理大区名称
     ,dim.agent_code  as receive_proxy_code        --收件代理区编码
     ,dim.agent_name  as receive_proxy             --收件代理区名称
     ,dim.fran_code   as receive_franchisee_code   --收件加盟商编码
     ,dim.fran_name   as receive_franchisee        --收件加盟商名称
     ,wide.early_scan_site_code         as receive_network_code      --收件网点编码
     ,dim.name                          as receive_network_name      --收件网点名称
     ,case when base.collect_proxy_code = dim.agent_code then 1 else 0 end as is_diff_proxy --是否跨代理区交件
     ,substr(wide.early_scan_time,12,8) as scan_time --收件时间
     ,to_date(wide.early_scan_time)  as scan_date --收件日期
     ,base.actual_center_shipment_no as actual_center_shipment_no --实际首中心到件任务号     (20220903新增)
     ,wide.customer_code             as customer_code             --客户编号                 (20220903新增)
     ,wide.express_type_code         as express_type_code         --产品类型编码             (20221103新增)
     ,wide.express_type_name         as express_type_name         --产品类型名称             (20221103新增)
     ,dim_last.area_id               as last_area_code            --目的区/县编码            (20221113新增)
     ,dim_last.area_desc             as last_area_name            --目的区/县名称            (20221113新增)
     ,dim_last.city_id               as last_city_code            --目的城市编码             (20221113新增)
     ,dim_last.city_desc             as last_city_name            --目的城市名称             (20221113新增)
     ,dim_last.agent_code            as last_agent_code           --目的代理区编码           (20221113新增)
     ,dim_last.agent_name            as last_agent_name           --目的代理区名称           (20221113新增)
     ,base.first_nodal_send_time     as first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
     ,base.first_nodal_arrival_time  as first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
     ,base.first_center_send_time    as first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
     ,base.first_center_arrival_time as first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
     ,case when wide.if_yiqin = 1 and base.first_center_code is null then 1 else 0 end as if_yiqin_late --疫情滞留(20221213新增)
     ,wide.customer_name             as customer_name --客户名称
     ,base.first_nodal_agent_code    as first_nodal_agent_code --实际交件集散点代理区编码
     ,base.first_nodal_agent_name    as first_nodal_agent_name --实际交件集散点代理区名称
     ,base.first_nodal_fran_code     as first_nodal_fran_code  --实际交件集散点加盟商编码
     ,base.first_nodal_fran_name     as first_nodal_fran_name  --实际交件集散点加盟商名称
     ,base.actual_center_shipment_shifts as actual_center_shipment_shifts--实际交件支线任务班次 20230420
     ,wide.taking_user_code          as taking_user_code --揽收业务员
     ,wide.taking_user_name          as taking_user_name --揽收业务员
     ,base.actual_network_shipment_no as actual_network_shipment_no   -- 网点到件发件任务单
     ,base.actual_nodal_shipment_no   as actual_nodal_shipment_no     -- 集散到件发件任务单
     ,wide.order_mark_en              as order_mark_en  -- 订单标签英文
     ,wide.order_mark_zh              as order_mark_zh  -- 订单标签中文
from (
    select
         wide.*
        ,tak.taking_user_code  --揽收业务员
        ,tak.taking_user_name  --揽收业务员
        ,order_mark.order_mark_en -- 订单标签英文
        ,order_mark.order_mark_zh -- 订单标签中文
    from (
        select
             waybill_no              as waybill_no
            ,goods_type_code         as goods_type_code    --物品类型编码
            ,goods_type_name         as goods_type_name    --物品类型名称
            ,customer_code           as customer_code      --客户编号
            ,customer_name           as customer_name      --客户名称
            ,dispatch_network_code   as last_network_code  --目的网点编码
            ,dispatch_network_name   as last_network_name  --目的网点名称
            ,express_type_code       as express_type_code  --产品类型编码
            ,express_type_name       as express_type_name  --产品类型名称
            ,case when ordersource_name in ('紫金山','逍遥峰','桃花岛','七星潭','桃花岛跨境','桃花岛跨境（逆）','极地湾','紫金山官方代发')
                  then ordersource_name else '其他' end as order_source --订单来源名称
            ,least(taking_early_scantime,warehousing_early_scantime) as early_scan_time --最早揽收时间
            ,nvl(taking_early_scansitecode,warehousing_early_scansitecode) as early_scan_site_code --收件网点
            ,case when is_difficult = 1 and difficult_type_code in ('66e','66g') then 1 else 0 end as if_yiqin --疫情滞留(20221213新增)
        from jms_dwd.dwd_wide_unsign_summary_waybill_dt
        where dt between date_sub('{{ execution_date | cst_ds }}',14) and '{{ execution_date | cst_ds }}'
          and to_date(least(taking_early_scantime,warehousing_early_scantime)) between date_sub('{{ execution_date | cst_ds }}',1) and '{{ execution_date | cst_ds }}' --T-2揽收入仓
          and is_intercept <> 1 --剔除拦截件
          and is_end_piece <> 1 --剔除完结件
    ) wide
    left join (
        select *
        from(
            select
                 waybill_no
                ,scan_site_code
                ,deliver_user_code as taking_user_code --揽收业务员
                ,deliver_user      as taking_user_name --揽收业务员
                ,row_number() over(partition by waybill_no order by scan_time asc) as rn
            from jms_dwd.dwd_tab_barscan_taking_base_dt
            where dt between date_sub('{{ execution_date | cst_ds }}',1) and '{{ execution_date | cst_ds }}'
             and to_date(scan_time) between date_sub('{{ execution_date | cst_ds }}',1) and '{{ execution_date | cst_ds }}'
        ) a where a.rn = 1
    ) tak on wide.waybill_no = tak.waybill_no
         and wide.early_scan_site_code = nvl(tak.scan_site_code,tak.waybill_no)
    left join ( -- 获取订单标签
        select
            a.waybill_id
            ,a.field_name  as order_mark_en  -- 订单标签英文
            ,b.value       as order_mark_zh  -- 订单标签中文
            ,row_number() over(partition by a.waybill_id order by if(a.field_name = 'reverseMail', 1 ,0) desc,a.update_time desc) as rn
        from
            jms_dwd.dwd_yl_oms_order_mark_expand_base_dt a
                left join jms_dim.dim_yl_oms_system_dict_base b on a. field_name = b.label and b.is_delete = 0
        where
            dt between date_add('{{ execution_date | cst_ds }}', -14) and '{{ execution_date | cst_ds }}'
    ) order_mark
        on wide.waybill_no = order_mark.waybill_id and order_mark.rn = 1
    where
        nvl(order_mark.order_mark_zh,'*') <> '桃花岛逆向上门取件'  -- 20231007剔除桃花岛逆向上门取件数据
) wide
left join (
    select
          base.*
         ,dim.agent_code as collect_proxy_code
         ,dim.agent_name as collect_proxy
         ,dim_nodal.agent_code as first_nodal_agent_code --代理区编码
         ,dim_nodal.agent_name as first_nodal_agent_name --代理区名称
         ,dim_nodal.fran_code  as first_nodal_fran_code  --加盟商编码
         ,dim_nodal.fran_name  as first_nodal_fran_name  --加盟商名称
    from bidefault.dwd_tab_barscan_arrival_send_base_dt base --到件发件合并数据
    left join jms_dim.dim_network_whole_massage dim --网点维度表
       on base.actual_center_code = dim.code
    left join jms_dim.dim_network_whole_massage dim_nodal --取实际交件集散
       on case when base.actual_collect_time < nvl(base.actual_center_time,'2099-12-31 00:00:00')
               then base.actual_collect_code end = dim_nodal.code
) base on wide.waybill_no = base.waybill_no
left join (
    select
          code
         ,name
         ,city_id
         ,city_desc
         ,manage_code
         ,manage_name
         ,agent_code
         ,agent_name
         ,fran_code
         ,fran_name
         ,network_type
    from jms_dim.dim_network_whole_massage --网点维度表
) dim on wide.early_scan_site_code = dim.code
left join (
    select
          code
         ,name
         ,area_id         --区/县ID
         ,area_desc       --区/县
         ,city_id
         ,city_desc
         ,manage_code
         ,manage_name
         ,agent_code
         ,agent_name
         ,fran_code
         ,fran_name
         ,network_type
    from jms_dim.dim_network_whole_massage --网点维度表
) dim_last on wide.last_network_code = dim_last.code;






-------------------------------路由打标
-- insert overwrite table bidefault.dwd_route_for_submit_tmp
-- select
--      in_from_code          --始发网点编码
--     ,in_from_name       --始发网点名称
--     ,in_collect_code    --始发集散点编码
--     ,in_collect_name    --始发集散点名称
--     ,in_to_code         --始发中心编码
--     ,in_to_name         --始发中心名称
--     ,out_to_code        --目的网点编码
--     ,out_to_name        --目的网点名称
--     ,out_from_code      --目的中心编码
--     ,out_from_name      --目的中心名称
--     ,last_taking_time   --揽收结束时间
--     ,network_plan_send_time    --始发网点规划发车时间
--     ,nodal_plan_arrival_time   --始发集散规划到达时间
--     ,nodal_plan_send_time      --始发集散规划发车时间
--     ,center_plan_arrival_time  --始发中心规划到达时间
--     ,center_plan_send_time     --始发中心/目的集散规划发车时间
--     ,case when flag = 1 then nodal_plan_arrival_time
--           when flag = 2 then null
--           when flag = 3 then nodal_plan_arrival_time
--           when flag = 4 then null
--           end as first_nodal_plan_arrival_time --集散规划到达时间
--     ,case when flag = 1 then (reserve_1 + in_line_day + in_edge_span_days)
--           when flag = 2 then 0
--           when flag = 3 then (reserve_1 + in_line_day)
--           when flag = 4 then (reserve_1 + in_line_day)
--           end as nodal_span_days --集散跨天
--     ,case when flag = 1 then center_plan_arrival_time
--           when flag = 2 then nodal_plan_arrival_time
--           when flag = 3 then null
--           when flag = 4 then null
--           end as first_center_plan_arrival_time --中心规划到达时间
--     ,case when flag = 1 then (reserve_1 + in_line_day + in_edge_span_days + in_edge2_stop_days + in_edge2_span_days)
--           when flag = 2 then (reserve_1 + in_line_day + in_edge_span_days)
--           when flag = 3 then 0
--           when flag = 4 then (reserve_1 + in_line_day)
--           end as center_span_days --中心跨天
--     ,reserve_1                           --揽收时间_收件时间_收件跨天
--     ,in_line_day                         --收件时间_网点发_衔接跨天
--     ,in_edge_span_days                   --网点发-集散1到_运输跨天
--     ,in_edge2_stop_days                  --集散1到-集散1发_衔接跨天
--     ,in_edge2_span_days                  --集散1发-集散2到/中心1_运输跨天
--     ,in_edge3_stop_days                  --网点/集散1/中心1到-网点/集散/中心1发_衔接跨天
--     ,branch_in_center_span_days          --中心到车1-中心发车1_衔接跨天
--     ,e1_end_code                         --第二个中心
--     ,total_days_t                        --T+X时效 (20220903新增)
--     ,first_center_span_stop_days         --网点-中心-网点场景,补充中心到车与中心发车时间衔接跨天
--     ,is_off                              --是否截单
--     ,first_warehousing_time              --第一个最晚揽收时间
--     ,nvl(lead_warehousing_time,network_lasted_warehousing_time) as lead_warehousing_time  --下一个最晚入仓时间
--     ,lasted_warehousing_time             --网点-集散-中心-网点最后一个最晚揽收时间
--     ,network_lasted_warehousing_time     --网点-网点最后一个最晚揽收时间
--     ,network_rk                          --网点-网点去重标识
--     ,nodal_rk                            --网点-集散-网点去重标识
--     ,center_rk                           --网点-中心-网点去重标识
--     ,flag                                --标签
--     ,update_date
-- from(
--      select
--           in_from_code       --始发网点编码
--          ,in_from_name       --始发网点名称
--          ,in_collect_code    --始发集散点编码
--          ,in_collect_name    --始发集散点名称
--          ,in_to_code         --始发中心编码
--          ,in_to_name         --始发中心名称
--          ,out_to_code        --目的网点编码
--          ,out_to_name        --目的网点名称
--          ,out_from_code      --目的中心编码
--          ,out_from_name      --目的中心名称
--          ,concat(substr(latest_warehousing_time_quantum,7,5),':00') as last_taking_time  --揽收结束时间
--          ,concat(in_edge_planned_departure  ,':00') as network_plan_send_time      --始发网点规划发车时间
--          ,concat(in_edge_planned_arrival    ,':00') as nodal_plan_arrival_time     --始发集散规划到达时间
--          ,concat(in_edge2_planned_departure ,':00') as nodal_plan_send_time        --始发集散规划发车时间
--          ,concat(in_edge2_planned_arrival   ,':00') as center_plan_arrival_time    --始发中心规划到达时间
--          ,first_center_plan_send_time               as center_plan_send_time       --始发中心/目的集散规划发车时间
--          ,reserve_1                   --揽收时间_收件时间_收件跨天
--          ,in_line_day                 --收件时间_网点发_衔接跨天
--          ,in_edge_span_days           --网点发-集散1到_运输跨天
--          ,in_edge2_stop_days          --集散1到-集散1发_衔接跨天
--          ,in_edge2_span_days          --集散1发-集散2到/中心1_运输跨天
--          ,in_edge3_stop_days          --网点/集散1/中心1到-网点/集散/中心1发_衔接跨天
--          ,branch_in_center_span_days  --中心到1-中心发1_衔接跨天
--          ,e1_end_code                 --第二个中心
--          ,total_days_t                --T+X时效 (20220903新增)
--          ,case when coalesce(e1_planned_departure,out_edge_planned_departure,extra_out_planned_departure,out_edge2_planned_departure) <= nvl(in_edge2_planned_arrival,in_edge_planned_arrival) then 1 else 0 end as first_center_span_stop_days --网点-中心-网点场景,补充中心到车与中心发车时间衔接跨天
--          ,if(first_value(substr(latest_warehousing_time_quantum,7,5)) over (partition by dt,start_network_code order by substr(latest_warehousing_time_quantum,7,5)) between '00:00' and '06:00',0,1) as is_off  --是否截单
--          ,concat(lead(substr(latest_warehousing_time_quantum,7,5)) over (partition by dt,in_from_code,in_collect_code,in_to_code,out_to_code order by substr(latest_warehousing_time_quantum,7,5))       ,':00') as lead_warehousing_time   --下一个最晚揽收时间
--          ,concat(first_value(substr(latest_warehousing_time_quantum,7,5)) over (partition by dt,in_from_code,in_collect_code,in_to_code,out_to_code order by substr(latest_warehousing_time_quantum,7,5)),':00') as first_warehousing_time  --第一个最晚揽收时间
--          ,concat(first_value(substr(latest_warehousing_time_quantum,7,5)) over (partition by dt,in_from_code,in_collect_code,in_to_code,out_to_code order by substr(latest_warehousing_time_quantum,7,5) desc),':00') as lasted_warehousing_time --最后一个最晚揽收时间
--          ,concat(first_value(substr(latest_warehousing_time_quantum,7,5)) over (partition by dt,in_from_code,out_to_code order by substr(latest_warehousing_time_quantum,7,5) desc),':00') as network_lasted_warehousing_time --最后一个最晚揽收时间
--          ,row_number() over(partition by dt,in_from_code,out_to_code order by substr(total_days_t,3,2),substr(latest_warehousing_time_quantum,7,5),in_edge_planned_departure,in_edge2_planned_departure,e1_planned_departure) as network_rk ---网点-网点去重标识
--          ,row_number() over(partition by dt,in_from_code,in_to_code,out_to_code order by substr(total_days_t,3,2),substr(latest_warehousing_time_quantum,7,5),in_edge_planned_departure,in_edge2_planned_departure,e1_planned_departure) as center_rk --网点-中心-网点去重标识
--          ,row_number() over(partition by dt,in_from_code,in_collect_code,out_to_code order by substr(total_days_t,3,2),substr(latest_warehousing_time_quantum,7,5),in_edge_planned_departure,in_edge2_planned_departure,e1_planned_departure) as nodal_rk --网点-集散-网点去重标识
--          ,case when in_from_code is not null and in_collect_code is not null and in_to_code is not null then 1  --网点-集散-中心-网点
--                when in_from_code is not null and in_collect_code is null and in_to_code is not null then 2 --网点-中心-网点
--                when in_from_code is not null and in_collect_code is not null and in_to_code is null then 3 --网点-集散-网点
--                when in_from_code is not null and in_collect_code is null and in_to_code is null then 4 --网点-网点
--                end as flag --标签
--          ,update_date
--      from jms_dm.dm_route_lastest_for_submit  --新兜底路由
--      where dt between date_sub('{{ execution_date | cst_ds }}',6) and date_sub('{{ execution_date | cst_ds }}',1)
-- ) a
-- ;




-----------------------根据始发网点、目的网点，关联兜底路由，拿出路由相关数据，并将三段码问题、规划问题、自收自派数据打标
-----------------------三段码问题、规划问题、自收自派
insert overwrite table bidefault.dwd_first_route_for_submit
select
     wide.waybill_no                --运单号
    ,wide.order_source              --订单来源名称
    ,wide.goods_type_code           --物品类型编码
    ,wide.goods_type_name           --物品类型名称
    ,wide.last_network_code         --运单表目的网点编码
    ,wide.last_network_name         --运单表目的网点名称
    ,wide.early_scan_time           --最早扫描时间
    ,wide.early_scan_site_code      --最早的收件网点
    ,wide.first_network_code        --始发网点编码
    ,wide.first_nodal_code          --始发集散编码
    ,wide.first_nodal_name          --始发集散名称
    ,wide.first_center_code         --始发中心编码
    ,wide.first_center_name         --始发中心名称
    ,wide.actual_center_code        --实际交件中心编码
    ,wide.actual_center_name        --实际交件中心名称
    ,wide.collect_proxy_code        --交件代理区编码
    ,wide.collect_proxy             --交件代理区名称
    ,wide.actual_send_time          --网点实际发件时间
    ,wide.actual_collect_code       --实际交件集散点编码
    ,wide.actual_collect_name       --实际交件集散点名称
    ,wide.plan_collect_time         --规划到达集散时间
    ,wide.plan_collect_code         --规划到达集散编码
    ,wide.plan_collect_name         --规划到达集散名称
    ,wide.actual_collect_arr_time   --实际交件集散点时间
    ,wide.actual_center_arr_time    --实际首中心到车时间
    ,wide.receive_city_id           --收件城市id
    ,wide.receive_city              --收件城市
    ,wide.region_code               --收件管理大区编码
    ,wide.region_name               --收件管理大区名称
    ,wide.receive_proxy_code        --收件代理区编码
    ,wide.receive_proxy             --收件代理区名称
    ,wide.receive_franchisee_code   --收件加盟商编码
    ,wide.receive_franchisee        --收件加盟商名称
    ,wide.receive_network_code      --收件网点编码
    ,wide.receive_network_name      --收件网点名称
    ,wide.is_diff_proxy             --是否跨代理区交件
    ,wide.scan_time
    ,wide.scan_date
    ,case when wide.last_network_code is null then 1 --三段码问题:无目的网点
          when wide.early_scan_site_code = wide.last_network_code then 3 --其他问题:自收自派
          when route.in_from_code is null or route.last_taking_time is null then 2 --规划问题:无路由
          else 0 end as type
    ,case when route.is_off = 1 then route.network_lasted_warehousing_time end as truncated_time --截单时间
    ,case when route.is_off = 1 and wide.scan_time > route.network_lasted_warehousing_time then 1 else 0 end as is_interrupted  --是否截单
    ,wide.actual_center_shipment_no as actual_center_shipment_no --实际首中心到件任务号     (20220903新增)
    ,wide.customer_code             as customer_code             --客户编号                 (20220903新增)
    ,wide.express_type_code         as express_type_code         --产品类型编码             (20221103新增)
    ,wide.express_type_name         as express_type_name         --产品类型名称             (20221103新增)
    ,wide.last_area_code            as last_area_code            --目的区/县编码            (20221113新增)
    ,wide.last_area_name            as last_area_name            --目的区/县名称            (20221113新增)
    ,wide.last_city_code            as last_city_code            --目的城市编码             (20221113新增)
    ,wide.last_city_name            as last_city_name            --目的城市名称             (20221113新增)
    ,wide.last_agent_code           as last_agent_code           --目的代理区编码           (20221113新增)
    ,wide.last_agent_name           as last_agent_name           --目的代理区名称           (20221113新增)
    ,wide.first_nodal_send_time     as first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
    ,wide.first_nodal_arrival_time  as first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
    ,wide.first_center_send_time    as first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
    ,wide.first_center_arrival_time as first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
    ,wide.if_yiqin_late             as if_yiqin_late             --是否疫情件               (20221213新增)
    ,route.total_days_t             as fast_total_days           --网点-网点T+X时效         (20221213新增)
    ,wide.customer_name             as customer_name             --客户名称
    ,wide.first_nodal_agent_code    as first_nodal_agent_code    --实际交件集散点代理区编码
    ,wide.first_nodal_agent_name    as first_nodal_agent_name    --实际交件集散点代理区名称
    ,wide.first_nodal_fran_code     as first_nodal_fran_code     --实际交件集散点加盟商编码
    ,wide.first_nodal_fran_name     as first_nodal_fran_name     --实际交件集散点加盟商名称
    ,wide.actual_center_shipment_shifts as actual_center_shipment_shifts--实际交件支线任务班次 20230420
    ,wide.taking_user_code          as taking_user_code --揽收业务员 20230516
    ,wide.taking_user_name          as taking_user_name --揽收业务员 20230516
    ,wide.actual_network_shipment_no  as actual_network_shipment_no   -- 网点到件发件任务单
    ,wide.actual_nodal_shipment_no    as actual_nodal_shipment_no     -- 集散到件发件任务单
    ,wide.order_mark_en               as order_mark_en  -- 订单标签英文
    ,wide.order_mark_zh               as order_mark_zh  -- 订单标签中文
from bidefault.dwd_tab_barscan_wdie_base_dt wide --揽收数据
left join jms_tmp.dwd_route_for_submit_tmp route
   on wide.early_scan_site_code = route.in_from_code  --按照始发网点,目的网点关联兜底路由数据
  and wide.last_network_code = route.out_to_code
  and route.network_rk = 1
  and wide.scan_date = route.update_date;








-----------------------同城件打标
insert overwrite table bidefault.dwd_first_route_city_for_submit
select
     waybill_no                --运单号
    ,order_source              --订单来源名称
    ,goods_type_code           --物品类型编码
    ,goods_type_name           --物品类型名称
    ,last_network_code         --运单表目的网点编码
    ,last_network_name         --运单表目的网点名称
    ,early_scan_time           --最早扫描时间
    ,early_scan_site_code      --最早的收件网点
    ,first_nodal_code          --始发集散编码
    ,first_nodal_name          --始发集散名称
    ,first_center_code         --始发中心编码
    ,first_center_name         --始发中心名称
    ,actual_center_code        --实际交件中心编码
    ,actual_center_name        --实际交件中心名称
    ,collect_proxy_code        --交件代理区编码
    ,collect_proxy             --交件代理区名称
    ,actual_send_time          --网点实际发件时间
    ,actual_collect_code       --实际交件集散点编码
    ,actual_collect_name       --实际交件集散点名称
    ,plan_collect_code         --规划到达集散编码
    ,plan_collect_name         --规划到达集散名称
    ,actual_collect_arr_time   --实际交件集散点时间
    ,actual_center_arr_time    --实际首中心到车时间
    ,receive_city_id           --收件城市id
    ,receive_city              --收件城市
    ,region_code               --收件管理大区编码
    ,region_name               --收件管理大区名称
    ,receive_proxy_code        --收件代理区编码
    ,receive_proxy             --收件代理区名称
    ,receive_franchisee_code   --收件加盟商编码
    ,receive_franchisee        --收件加盟商名称
    ,receive_network_code      --收件网点编码
    ,receive_network_name      --收件网点名称
    ,is_diff_proxy             --是否跨代理区交件
    ,scan_time                 --扫描时间
    ,scan_date                 --扫描日期
    ,max(type)           as type
    ,max(truncated_time) as truncated_time            --截单时间
    ,max(is_interrupted) as is_interrupted            --是否截单
    ,actual_center_shipment_no  --实际首中心到件任务号（20220903新增）
    ,customer_code              --客户编号（20220903新增）
    ,express_type_code         --产品类型编码             (20221103新增)
    ,express_type_name         --产品类型名称             (20221103新增)
    ,last_area_code            --目的区/县编码            (20221113新增)
    ,last_area_name            --目的区/县名称            (20221113新增)
    ,last_city_code            --目的城市编码             (20221113新增)
    ,last_city_name            --目的城市名称             (20221113新增)
    ,last_agent_code           --目的代理区编码           (20221113新增)
    ,last_agent_name           --目的代理区名称           (20221113新增)
    ,first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
    ,first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
    ,first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
    ,first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
    ,if_yiqin_late             --是否疫情件               (20221213新增)
    ,fast_total_days           --网点-网点T+X时效         (20221213新增)
    ,customer_name             --客户名称
    ,first_nodal_agent_code    --实际交件集散点代理区编码
    ,first_nodal_agent_name    --实际交件集散点代理区名称
    ,first_nodal_fran_code     --实际交件集散点加盟商编码
    ,first_nodal_fran_name     --实际交件集散点加盟商名称
    ,actual_center_shipment_shifts--实际交件支线任务班次 20230420新增
    ,taking_user_code          --揽收业务员 20230516
    ,taking_user_name          --揽收业务员 20230516
    ,actual_network_shipment_no  as actual_network_shipment_no   -- 网点到件发件任务单
    ,actual_nodal_shipment_no    as actual_nodal_shipment_no     -- 集散到件发件任务单
    ,order_mark_en             -- 订单标签英文
    ,order_mark_zh             -- 订单标签中文
from (
    select
          wide.waybill_no                --运单号
         ,wide.order_source              --订单来源名称
         ,wide.goods_type_code           --物品类型编码
         ,wide.goods_type_name           --物品类型名称
         ,wide.last_network_code         --运单表目的网点编码
         ,wide.last_network_name         --运单表目的网点名称
         ,wide.early_scan_time           --最早扫描时间
         ,wide.early_scan_site_code      --最早的收件网点
         ,wide.first_network_code        --始发网点编码
         ,wide.first_nodal_code          --始发集散编码
         ,wide.first_nodal_name          --始发集散名称
         ,wide.first_center_code         --始发中心编码
         ,wide.first_center_name         --始发中心名称
         ,wide.actual_center_code        --实际交件中心编码
         ,wide.actual_center_name        --实际交件中心名称
         ,wide.collect_proxy_code        --交件代理区编码
         ,wide.collect_proxy             --交件代理区名称
         ,wide.actual_send_time          --网点实际发件时间
         ,wide.actual_collect_code       --实际交件集散点编码
         ,wide.actual_collect_name       --实际交件集散点名称
         ,wide.plan_collect_time         --规划到达集散时间
         ,wide.plan_collect_code         --规划到达集散编码
         ,wide.plan_collect_name         --规划到达集散名称
         ,wide.actual_collect_arr_time   --实际交件集散点时间
         ,wide.actual_center_arr_time    --实际首中心到车时间
         ,wide.receive_city_id           --收件城市id
         ,wide.receive_city              --收件城市
         ,wide.region_code               --收件管理大区编码
         ,wide.region_name               --收件管理大区名称
         ,wide.receive_proxy_code        --收件代理区编码
         ,wide.receive_proxy             --收件代理区名称
         ,wide.receive_franchisee_code   --收件加盟商编码
         ,wide.receive_franchisee        --收件加盟商名称
         ,wide.receive_network_code      --收件网点编码
         ,wide.receive_network_name      --收件网点名称
         ,wide.is_diff_proxy             --是否跨代理区交件
         ,wide.scan_time
         ,wide.scan_date
         ,case when route.in_from_code is not null and wide.actual_center_arr_time is null then 4 else 0 end as type --同城件
         ,case when route.is_off = 1 and route.e1_end_code is null then route.last_taking_time end as truncated_time --截单时间
         ,case when route.is_off = 1 and route.e1_end_code is null and wide.scan_time > route.lasted_warehousing_time then 1 else 0 end as is_interrupted  --是否截单
         ,wide.actual_center_shipment_no as actual_center_shipment_no --实际首中心到件任务号     (20220903新增)
         ,wide.customer_code             as customer_code             --客户编号                 (20220903新增)
         ,wide.express_type_code         as express_type_code         --产品类型编码             (20221103新增)
         ,wide.express_type_name         as express_type_name         --产品类型名称             (20221103新增)
         ,wide.last_area_code            as last_area_code            --目的区/县编码            (20221113新增)
         ,wide.last_area_name            as last_area_name            --目的区/县名称            (20221113新增)
         ,wide.last_city_code            as last_city_code            --目的城市编码             (20221113新增)
         ,wide.last_city_name            as last_city_name            --目的城市名称             (20221113新增)
         ,wide.last_agent_code           as last_agent_code           --目的代理区编码           (20221113新增)
         ,wide.last_agent_name           as last_agent_name           --目的代理区名称           (20221113新增)
         ,wide.first_nodal_send_time     as first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
         ,wide.first_nodal_arrival_time  as first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
         ,wide.first_center_send_time    as first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
         ,wide.first_center_arrival_time as first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
         ,wide.if_yiqin_late             as if_yiqin_late             --是否疫情件               (20221213新增)
         ,wide.fast_total_days           as fast_total_days           --网点-网点T+X时效         (20221213新增)
         ,wide.customer_name             as customer_name             --客户名称
         ,wide.first_nodal_agent_code    as first_nodal_agent_code    --实际交件集散点代理区编码
         ,wide.first_nodal_agent_name    as first_nodal_agent_name    --实际交件集散点代理区名称
         ,wide.first_nodal_fran_code     as first_nodal_fran_code     --实际交件集散点加盟商编码
         ,wide.first_nodal_fran_name     as first_nodal_fran_name     --实际交件集散点加盟商名称
         ,wide.actual_center_shipment_shifts as actual_center_shipment_shifts--实际交件支线任务班次 20230420新增
         ,wide.taking_user_code          as taking_user_code --揽收业务员 20230516
         ,wide.taking_user_name          as taking_user_name --揽收业务员 20230516
         ,wide.actual_network_shipment_no  as actual_network_shipment_no   -- 网点到件发件任务单
         ,wide.actual_nodal_shipment_no    as actual_nodal_shipment_no     -- 集散到件发件任务单
         ,wide.order_mark_en               as order_mark_en  -- 订单标签英文
         ,wide.order_mark_zh               as order_mark_zh  -- 订单标签中文
     from (
        select *
        from bidefault.dwd_first_route_for_submit
        where type = 0 --剔除三种异常件
    ) wide
    left join (
        select *
        from jms_tmp.dwd_route_for_submit_tmp  --兜底路由 始发网点、始发集散、始发中心、目的中心
        where e1_end_code is null
    ) route on wide.early_scan_site_code = route.in_from_code  --按照始发网点,目的网点关联兜底路由数据
           and wide.last_network_code = route.out_to_code
           and wide.scan_date = route.update_date
) a group by waybill_no                --运单号
            ,order_source              --订单来源名称
            ,goods_type_code           --物品类型编码
            ,goods_type_name           --物品类型名称
            ,last_network_code         --运单表目的网点编码
            ,last_network_name         --运单表目的网点名称
            ,early_scan_time           --最早扫描时间
            ,early_scan_site_code      --最早的收件网点
            ,first_network_code        --始发网点编码
            ,first_nodal_code          --始发集散编码
            ,first_nodal_name          --始发集散名称
            ,first_center_code         --始发中心编码
            ,first_center_name         --始发中心名称
            ,actual_center_code        --实际交件中心编码
            ,actual_center_name        --实际交件中心名称
            ,collect_proxy_code        --交件代理区编码
            ,collect_proxy             --交件代理区名称
            ,actual_send_time          --网点实际发件时间
            ,actual_collect_code       --实际交件集散点编码
            ,actual_collect_name       --实际交件集散点名称
            ,plan_collect_time         --规划到达集散时间
            ,plan_collect_code         --规划到达集散编码
            ,plan_collect_name         --规划到达集散名称
            ,actual_collect_arr_time   --实际交件集散点时间
            ,actual_center_arr_time    --实际首中心到车时间
            ,receive_city_id           --收件城市id
            ,receive_city              --收件城市
            ,region_code               --收件管理大区编码
            ,region_name               --收件管理大区名称
            ,receive_proxy_code        --收件代理区编码
            ,receive_proxy             --收件代理区名称
            ,receive_franchisee_code   --收件加盟商编码
            ,receive_franchisee        --收件加盟商名称
            ,receive_network_code      --收件网点编码
            ,receive_network_name      --收件网点名称
            ,is_diff_proxy             --是否跨代理区交件
            ,scan_time                 --扫描时间
            ,scan_date                 --扫描日期
            ,actual_center_shipment_no --实际首中心到件任务号     (20220903新增)
            ,customer_code             --客户编号                 (20220903新增)
            ,express_type_code         --产品类型编码             (20221103新增)
            ,express_type_name         --产品类型名称             (20221103新增)
            ,last_area_code            --目的区/县编码            (20221113新增)
            ,last_area_name            --目的区/县名称            (20221113新增)
            ,last_city_code            --目的城市编码             (20221113新增)
            ,last_city_name            --目的城市名称             (20221113新增)
            ,last_agent_code           --目的代理区编码           (20221113新增)
            ,last_agent_name           --目的代理区名称           (20221113新增)
            ,first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
            ,first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
            ,first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
            ,first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
            ,if_yiqin_late             --是否疫情件               (20221213新增)
            ,fast_total_days           --网点-网点T+X时效         (20221213新增)
            ,customer_name             --客户名称
            ,first_nodal_agent_code    --实际交件集散点代理区编码
            ,first_nodal_agent_name    --实际交件集散点代理区名称
            ,first_nodal_fran_code     --实际交件集散点加盟商编码
            ,first_nodal_fran_name     --实际交件集散点加盟商名称
            ,actual_center_shipment_shifts --实际首中心任务班次
            ,taking_user_code         --揽收业务员 20230516
            ,taking_user_name         --揽收业务员 20230516
            ,actual_network_shipment_no -- 网点到件发件任务单
            ,actual_nodal_shipment_no   -- 集散到件发件任务单
            ,order_mark_en            -- 订单标签英文
            ,order_mark_zh            -- 订单标签中文
;









-------------------------------第二段\根据始发网点、始发集散、始发中心、目的网点 关联兜底路由，拿出路由相关数据，并将错发数据打标
-------------------------------错发
insert overwrite table bidefault.dwd_second_error_route_for_submit
select
      wide.waybill_no                --运单号
     ,wide.order_source              --订单来源名称
     ,wide.goods_type_code           --物品类型编码
     ,wide.goods_type_name           --物品类型名称
     ,wide.last_network_code         --运单表目的网点编码
     ,wide.last_network_name         --运单表目的网点名称
     ,wide.early_scan_time           --最早扫描时间
     ,wide.early_scan_site_code      --最早扫描时间
     ,wide.first_nodal_code          --始发集散编码
     ,wide.first_nodal_name          --始发集散名称
     ,wide.first_center_code         --始发中心编码
     ,wide.first_center_name         --始发中心名称
     ,wide.actual_collect_code       --实际交件集散点编码
     ,wide.actual_collect_name       --实际交件集散点名称
     ,wide.actual_center_code        --实际交件中心编码
     ,wide.actual_center_name        --实际交件中心名称
     ,wide.collect_proxy_code        --交件代理区编码
     ,wide.collect_proxy             --交件代理区名称
     ,wide.actual_send_time          --网点实际发件时间
     ,wide.plan_collect_code         --规划到达集散编码
     ,wide.plan_collect_name         --规划到达集散名称
     ,wide.actual_collect_arr_time   --实际交件集散点时间
     ,wide.actual_center_arr_time    --实际首中心到车时间
     ,wide.receive_city_id           --收件城市id
     ,wide.receive_city              --收件城市
     ,wide.region_code               --收件管理大区编码
     ,wide.region_name               --收件管理大区名称
     ,wide.receive_proxy_code        --收件代理区编码
     ,wide.receive_proxy             --收件代理区名称
     ,wide.receive_franchisee_code   --收件加盟商编码
     ,wide.receive_franchisee        --收件加盟商名称
     ,wide.receive_network_code      --收件网点编码
     ,wide.receive_network_name      --收件网点名称
     ,wide.is_diff_proxy             --是否跨代理区
     ,wide.scan_time
     ,wide.scan_date
     ,case when wide.early_scan_site_code is not null and wide.first_nodal_code is null and wide.first_center_code is null then 0 --网点后无操作不定位为错发，直接判责
           when wide.early_scan_site_code is not null and wide.first_nodal_code is not null and wide.first_center_code is null then 0 --网点-集散后无操作不定位为错发，直接判责
           when route.in_from_code is null then 5
           else wide.type end as type --无路由:错发
     ,case when route.is_off = 1 then route.network_lasted_warehousing_time  end as truncated_time --截单时间
     ,case when route.is_off = 1 and wide.scan_time > route.network_lasted_warehousing_time then 1 else 0 end as is_interrupted  --是否截单
     ,wide.actual_center_shipment_no as actual_center_shipment_no --实际首中心到件任务号(20220903新增)
     ,wide.customer_code             as customer_code             --客户编号            (20220903新增)
     ,wide.express_type_code         as express_type_code         --产品类型编码        (20221103新增)
     ,wide.express_type_name         as express_type_name         --产品类型名称        (20221103新增)
     ,wide.last_area_code            as last_area_code            --目的区/县编码       (20221113新增)
     ,wide.last_area_name            as last_area_name            --目的区/县名称       (20221113新增)
     ,wide.last_city_code            as last_city_code            --目的城市编码        (20221113新增)
     ,wide.last_city_name            as last_city_name            --目的城市名称        (20221113新增)
     ,wide.last_agent_code           as last_agent_code           --目的代理区编码      (20221113新增)
     ,wide.last_agent_name           as last_agent_name           --目的代理区名称      (20221113新增)
     ,wide.first_nodal_send_time     as first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
     ,wide.first_nodal_arrival_time  as first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
     ,wide.first_center_send_time    as first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
     ,wide.first_center_arrival_time as first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
     ,wide.if_yiqin_late             as if_yiqin_late             --是否疫情件               (20221213新增)
     ,wide.fast_total_days           as fast_total_days           --网点-网点T+X时效         (20221213新增)
     ,wide.customer_name             as customer_name             --客户名称
     ,wide.first_nodal_agent_code    as first_nodal_agent_code    --实际交件集散点代理区编码
     ,wide.first_nodal_agent_name    as first_nodal_agent_name    --实际交件集散点代理区名称
     ,wide.first_nodal_fran_code     as first_nodal_fran_code     --实际交件集散点加盟商编码
     ,wide.first_nodal_fran_name     as first_nodal_fran_name     --实际交件集散点加盟商名称
     ,wide.actual_center_shipment_shifts as actual_center_shipment_shifts--实际交件支线任务班次 20230420新增
     ,wide.taking_user_code          as taking_user_code --揽收业务员 20230516
     ,wide.taking_user_name          as taking_user_name --揽收业务员 20230516
     ,wide.actual_network_shipment_no  as actual_network_shipment_no   -- 网点到件发件任务单
     ,wide.actual_nodal_shipment_no    as actual_nodal_shipment_no     -- 集散到件发件任务单
     ,wide.order_mark_en               as order_mark_en -- 订单标签英文
     ,wide.order_mark_zh               as order_mark_zh -- 订单标签中文
from (
    select *
    from bidefault.dwd_first_route_city_for_submit
    where type = 0   --有始发网点和目的网点
) wide
left join jms_tmp.dwd_route_for_submit_tmp route --兜底路由 始发网点、始发集散、始发中心、目的中心
   on wide.early_scan_site_code = route.in_from_code  --按照始发网点、始发中心、目的网点关联兜底路由数据
  and nvl(wide.first_center_code,0) = nvl(route.in_to_code,0)
  and wide.last_network_code = route.out_to_code
  and route.center_rk = 1
  and wide.scan_date = route.update_date
;






-------------------------------不打标，计算集散、中心规划到达时间
insert overwrite table bidefault.dwd_collect_route_for_submit
select
      waybill_no                --运单号
     ,order_source              --订单来源名称
     ,goods_type_code           --物品类型编码
     ,goods_type_name           --物品类型名称
     ,last_network_code         --运单表目的网点编码
     ,last_network_name         --运单表目的网点名称
     ,early_scan_time           --最早扫描时间
     ,early_scan_site_code      --最早扫描时间
     ,first_nodal_code          --始发集散编码
     ,first_nodal_name          --始发集散名称
     ,first_center_code         --始发中心编码
     ,first_center_name         --始发中心名称
     ,actual_collect_code       --实际交件集散点编码
     ,actual_collect_name       --实际交件集散点名称
     ,actual_center_code        --实际交件中心编码
     ,actual_center_name        --实际交件中心名称
     ,collect_proxy_code        --交件代理区编码
     ,collect_proxy             --交件代理区名称
     ,actual_send_time          --网点实际发件时间
     ,plan_collect_code         --规划到达集散编码
     ,plan_collect_name         --规划到达集散名称
     ,actual_collect_arr_time   --实际交件集散点时间
     ,null as actual_center_arr_time    --实际首中心到车时间
     ,receive_city_id           --收件城市id
     ,receive_city              --收件城市
     ,region_code               --收件管理大区编码
     ,region_name               --收件管理大区名称
     ,receive_proxy_code        --收件代理区编码
     ,receive_proxy             --收件代理区名称
     ,receive_franchisee_code   --收件加盟商编码
     ,receive_franchisee        --收件加盟商名称
     ,receive_network_code      --收件网点编码
     ,receive_network_name      --收件网点名称
     ,is_diff_proxy             --是否跨代理区
     ,scan_time                 --揽收时间
     ,scan_date                 --揽收日期
     ,type                      --
     ,route_truncated_time as truncated_time  --截单时间
     ,route_is_interrupted as is_interrupted  --是否截单
     ,plan_collect_arr_time            --始发集散规划到达时间
     ,plan_center_arr_time             --始发中心规划到达时间
     ,actual_center_shipment_no        --实际首中心到件任务号     (20220903新增)
     ,customer_code                    --客户编号                 (20220903新增)
     ,total_days_t                     --T+x时效                  (20220903新增)
     ,null as center_planned_departure --中心规划发车时间-1小时   (20220903新增)
     ,null as is_intime                --时效是否准点             (20220903新增)
     ,express_type_code                --产品类型编码             (20221103新增)
     ,express_type_name                --产品类型名称             (20221103新增)
     ,last_area_code                   --目的区/县编码            (20221113新增)
     ,last_area_name                   --目的区/县名称            (20221113新增)
     ,last_city_code                   --目的城市编码             (20221113新增)
     ,last_city_name                   --目的城市名称             (20221113新增)
     ,last_agent_code                  --目的代理区编码           (20221113新增)
     ,last_agent_name                  --目的代理区名称           (20221113新增)
     ,first_nodal_send_time            --始发集散发件扫描时间     (20221113新增)
     ,first_nodal_arrival_time         --始发集散到件扫描时间     (20221113新增)
     ,first_center_send_time           --始发转运中心发件扫描时间 (20221113新增)
     ,first_center_arrival_time        --始发转运中心到件扫描时间 (20221113新增)
     ,if_yiqin_late                    --是否疫情件               (20221213新增)
     ,fast_total_days                  --网点-网点T+X时效         (20221213新增)
     ,null as first_center_plan_send_time --始发中心规划发车时间
     ,customer_name                    --客户名称
     ,first_nodal_agent_code           --实际交件集散点代理区编码
     ,first_nodal_agent_name           --实际交件集散点代理区名称
     ,first_nodal_fran_code            --实际交件集散点加盟商编码
     ,first_nodal_fran_name            --实际交件集散点加盟商名称
     ,actual_center_shipment_shifts    --实际交件支线任务班次 20230420新增
     ,taking_user_code                 --揽收业务员 20230516
     ,taking_user_name                 --揽收业务员 20230516
     ,actual_network_shipment_no       --网点到件发件任务单
     ,actual_nodal_shipment_no         --集散到件发件任务单
     ,first_network_plan_send_time     --始发网点规划发车时间
     ,first_nodal_plan_send_time       --始发集散规划发车时间
     ,order_mark_en               as order_mark_en -- 订单标签英文
     ,order_mark_zh               as order_mark_zh -- 订单标签中文
from (
    select *,row_number() over(partition by waybill_no order by data_num asc) as rn
    from (
         select
               wide.*
              ,route.total_days_t as total_days_t --T+x时效
              ,concat(date_add(wide.scan_date,route.reserve_1 + route.in_line_day),' ',route.network_plan_send_time)             as first_network_plan_send_time -- 网点规划发车时间
              ,concat(date_add(wide.scan_date,route.nodal_span_days + route.in_edge2_stop_days),' ',route.nodal_plan_send_time)  as first_nodal_plan_send_time   -- 集散规划发车时间
              ,case when route.is_off = 1 then route.network_lasted_warehousing_time end as route_truncated_time --截单时间
              ,case when route.is_off = 1 and wide.scan_time > route.network_lasted_warehousing_time then 1 else 0 end as route_is_interrupted  --是否截单
              ,concat(date_add(wide.scan_date,nodal_span_days+if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)),' ',route.first_nodal_plan_arrival_time) as plan_collect_arr_time --始发集散规划到达时间
              ,null as plan_center_arr_time --始发中心规划到达时间
              ,case when wide.scan_time > route.network_lasted_warehousing_time then unix_timestamp(concat(date_add(wide.scan_date,1),' ',
                        if(route.last_taking_time >= '00:00:00' and route.last_taking_time <= '06:00:00',route.first_warehousing_time,
                        if(route.last_taking_time = route.network_lasted_warehousing_time,route.last_taking_time,null)))) - unix_timestamp(wide.early_scan_time)
                    when nvl(unix_timestamp(concat(wide.scan_date,' ',route.last_taking_time)),99999999999999999) - unix_timestamp(wide.early_scan_time) >0
                    then nvl(unix_timestamp(concat(wide.scan_date,' ',route.last_taking_time)),99999999999999999) - unix_timestamp(wide.early_scan_time)
                    else 99999999999999999
                    end as data_num
         from (
              select *
              from bidefault.dwd_second_error_route_for_submit
              where type = 0  --剔除错发数据
                and early_scan_site_code is not null
                and first_nodal_code is not null
                and first_center_code is  null
         ) wide
         left join jms_tmp.dwd_route_for_submit_tmp route
            on wide.early_scan_site_code = route.in_from_code  --按照始发网点、始发集散、始发中心、目的网点关联兜底路由数据
           and nvl(wide.first_nodal_code ,0) = nvl(route.in_collect_code,0)
           and wide.last_network_code = route.out_to_code
           and wide.scan_date = route.update_date
    ) t where data_num >0
) a where a.rn = 1;



-------------------------------不打标，计算集散、中心规划到达时间
insert overwrite table bidefault.dwd_three_route_for_submit
select
      waybill_no                --运单号
     ,order_source              --订单来源名称
     ,goods_type_code           --物品类型编码
     ,goods_type_name           --物品类型名称
     ,last_network_code         --运单表目的网点编码
     ,last_network_name         --运单表目的网点名称
     ,early_scan_time           --最早扫描时间
     ,early_scan_site_code      --最早扫描时间
     ,first_nodal_code          --始发集散编码
     ,first_nodal_name          --始发集散名称
     ,first_center_code         --始发中心编码
     ,first_center_name         --始发中心名称
     ,actual_collect_code       --实际交件集散点编码
     ,actual_collect_name       --实际交件集散点名称
     ,actual_center_code        --实际交件中心编码
     ,actual_center_name        --实际交件中心名称
     ,collect_proxy_code        --交件代理区编码
     ,collect_proxy             --交件代理区名称
     ,actual_send_time          --网点实际发件时间
     ,plan_collect_code         --规划到达集散编码
     ,plan_collect_name         --规划到达集散名称
     ,actual_collect_arr_time   --实际交件集散点时间
     ,actual_center_arr_time    --实际首中心到车时间
     ,receive_city_id           --收件城市id
     ,receive_city              --收件城市
     ,region_code               --收件管理大区编码
     ,region_name               --收件管理大区名称
     ,receive_proxy_code        --收件代理区编码
     ,receive_proxy             --收件代理区名称
     ,receive_franchisee_code   --收件加盟商编码
     ,receive_franchisee        --收件加盟商名称
     ,receive_network_code      --收件网点编码
     ,receive_network_name      --收件网点名称
     ,is_diff_proxy             --是否跨代理区
     ,scan_time                 --揽收时间
     ,scan_date                 --揽收日期
     ,type                      --
     ,route_truncated_time as truncated_time  --截单时间
     ,route_is_interrupted as is_interrupted  --是否截单
     ,plan_collect_arr_time       --始发集散规划到达时间
     ,plan_center_arr_time        --始发中心规划到达时间
     ,actual_center_shipment_no   --实际首中心到件任务号     (20220903新增)
     ,customer_code               --客户编号                 (20220903新增）
     ,total_days_t                --T+x时效                  (20220903新增)
     ,center_planned_departure    --中心规划发车时间-1小时   (20220903新增)
     ,is_intime                   --时效是否准点             (20220903新增)
     ,express_type_code           --产品类型编码             (20221103新增)
     ,express_type_name           --产品类型名称             (20221103新增)
     ,last_area_code              --目的区/县编码            (20221113新增)
     ,last_area_name              --目的区/县名称            (20221113新增)
     ,last_city_code              --目的城市编码             (20221113新增)
     ,last_city_name              --目的城市名称             (20221113新增)
     ,last_agent_code             --目的代理区编码           (20221113新增)
     ,last_agent_name             --目的代理区名称           (20221113新增)
     ,first_nodal_send_time       --始发集散发件扫描时间     (20221113新增)
     ,first_nodal_arrival_time    --始发集散到件扫描时间     (20221113新增)
     ,first_center_send_time      --始发转运中心发件扫描时间 (20221113新增)
     ,first_center_arrival_time   --始发转运中心到件扫描时间 (20221113新增)
     ,if_yiqin_late               --是否疫情件               (20221213新增)
     ,fast_total_days             --网点-网点T+X时效         (20221213新增)
     ,first_center_plan_send_time --始发中心规划发车时间     (20230103新增)
     ,customer_name                    --客户名称
     ,first_nodal_agent_code           --实际交件集散点代理区编码
     ,first_nodal_agent_name           --实际交件集散点代理区名称
     ,first_nodal_fran_code            --实际交件集散点加盟商编码
     ,first_nodal_fran_name            --实际交件集散点加盟商名称
     ,actual_center_shipment_shifts    --实际交件支线任务班次 20230420新增
     ,taking_user_code                 --揽收业务员 20230516
     ,taking_user_name                 --揽收业务员 20230516
     ,actual_network_shipment_no       -- 网点到件发件任务单
     ,actual_nodal_shipment_no         -- 集散到件发件任务单
     ,first_network_plan_send_time     --始发网点规划发车时间
     ,first_nodal_plan_send_time       --始发集散规划发车时间
     ,order_mark_en                    -- 订单标签英文
     ,order_mark_zh                    -- 订单标签中文
from (
     select
           *
          ,case when actual_center_arr_time <= center_planned_departure then 1 else 0 end as is_intime --时效是否准点（20220903新增）
          ,row_number() over(partition by waybill_no order by data_num asc) as rn
     from (
          select
                wide.*
               ,route.total_days_t as total_days_t --T+x时效
               ,from_unixtime(unix_timestamp(concat(date_add(wide.scan_date,route.center_span_days + route.in_edge3_stop_days),' ',route.center_plan_send_time))-3600,'yyyy-MM-dd HH:mm:ss') as center_planned_departure --中心规划发车时间-1小时（20220903新增）
               ,concat(date_add(wide.scan_date,route.center_span_days + route.in_edge3_stop_days),' ',route.center_plan_send_time) as first_center_plan_send_time --始发中心规划发车时间
               ,concat(date_add(wide.scan_date,route.reserve_1 + route.in_line_day),' ',route.network_plan_send_time)             as first_network_plan_send_time -- 始发网点规划发车时间
               ,concat(date_add(wide.scan_date,route.nodal_span_days + route.in_edge2_stop_days),' ',route.nodal_plan_send_time)  as first_nodal_plan_send_time   -- 始发集散规划发车时间
               ,case when route.is_off = 1 then route.network_lasted_warehousing_time end as route_truncated_time --截单时间
               ,case when route.is_off = 1 and wide.scan_time > route.network_lasted_warehousing_time then 1 else 0 end as route_is_interrupted  --是否截单
               ,concat(date_add(wide.scan_date,nodal_span_days+if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)),' ',route.first_nodal_plan_arrival_time) as plan_collect_arr_time --始发集散规划到达时间
               ,case when wide.scan_time < route.first_warehousing_time --揽收时间<最早的最晚入仓时间
                         then concat(date_add(wide.scan_date,if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)+route.center_span_days),' ',route.first_center_plan_arrival_time) --无截单补充揽收跨天
                     when wide.scan_time >=route.first_warehousing_time and wide.scan_time <= route.lead_warehousing_time --揽收时间<下一个最晚入仓时间
                         then concat(date_add(wide.scan_date,if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)+route.center_span_days),' ',route.first_center_plan_arrival_time) --无截单补充揽收跨天
                     when wide.scan_time > route.network_lasted_warehousing_time --揽收时间>最晚的最晚入仓时间
                         then (case when route.is_off = 0 then concat(date_add(wide.scan_date,route.center_span_days+1),' ',route.first_center_plan_arrival_time) --揽收时间 大于 最晚的最晚入仓时间，且无截单，正常取，直接+1天
                                    else from_unixtime(unix_timestamp(concat(date_add(wide.scan_date,route.center_span_days + if(route.branch_in_center_span_days <> 0,route.branch_in_center_span_days,route.in_edge3_stop_days)),' ',route.center_plan_send_time))-3600,'yyyy-MM-dd HH:mm:ss') --有截单，用中心规划发车时间-1小时
                         end)
                     end as plan_center_arr_time --始发中心规划到达时间
               ,case when wide.scan_time > route.network_lasted_warehousing_time
                         then unix_timestamp(concat(date_add(wide.scan_date,1),' ',
                         if(route.last_taking_time >= '00:00:00' and route.last_taking_time <= '06:00:00',route.first_warehousing_time,
                         if(route.last_taking_time = route.network_lasted_warehousing_time,route.last_taking_time,null)))) - unix_timestamp(wide.early_scan_time)
                     when nvl(unix_timestamp(concat(wide.scan_date,' ', route.last_taking_time)),99999999999999999) - unix_timestamp(wide.early_scan_time) >0
                         then nvl(unix_timestamp(concat(wide.scan_date,' ',route.last_taking_time)),99999999999999999) - unix_timestamp(wide.early_scan_time)
                     else 99999999999999999
                     end as data_num
          from (
               select *
               from bidefault.dwd_second_error_route_for_submit
               where type = 0  --剔除错发数据
                 and !(early_scan_site_code is not null
                 and first_nodal_code is not null
                 and first_center_code is  null)
          ) wide
          left join jms_tmp.dwd_route_for_submit_tmp route
             on wide.early_scan_site_code = route.in_from_code  --按照始发网点、始发集散、始发中心、目的网点关联兜底路由数据
            and nvl(wide.first_nodal_code ,0) = nvl(route.in_collect_code,0)
            and nvl(wide.first_center_code,0) = nvl(route.in_to_code     ,0)
            and wide.last_network_code = route.out_to_code
            and wide.scan_date = route.update_date
     ) t where data_num >0
) a where a.rn = 1;






-------------------------------不打标，计算集散、中心规划到达时间
insert overwrite table bidefault.dwd_four_route_for_submit
select
      waybill_no                  --运单号
     ,order_source                --订单来源名称
     ,goods_type_code             --物品类型编码
     ,goods_type_name             --物品类型名称
     ,last_network_code           --运单表目的网点编码
     ,last_network_name           --运单表目的网点名称
     ,early_scan_time             --最早扫描时间
     ,early_scan_site_code        --最早扫描时间
     ,first_nodal_code            --始发集散编码
     ,first_nodal_name            --始发集散名称
     ,first_center_code           --始发中心编码
     ,first_center_name           --始发中心名称
     ,actual_collect_code         --实际交件集散点编码
     ,actual_collect_name         --实际交件集散点名称
     ,actual_center_code          --实际交件中心编码
     ,actual_center_name          --实际交件中心名称
     ,collect_proxy_code          --交件代理区编码
     ,collect_proxy               --交件代理区名称
     ,actual_send_time            --网点实际发件时间
     ,plan_collect_code           --规划到达集散编码
     ,plan_collect_name           --规划到达集散名称
     ,actual_collect_arr_time     --实际交件集散点时间
     ,actual_center_arr_time      --实际首中心到车时间
     ,receive_city_id             --收件城市id
     ,receive_city                --收件城市
     ,region_code                 --收件管理大区编码
     ,region_name                 --收件管理大区名称
     ,receive_proxy_code          --收件代理区编码
     ,receive_proxy               --收件代理区名称
     ,receive_franchisee_code     --收件加盟商编码
     ,receive_franchisee          --收件加盟商名称
     ,receive_network_code        --收件网点编码
     ,receive_network_name        --收件网点名称
     ,is_diff_proxy               --是否跨代理区
     ,scan_time                   --揽收时间
     ,scan_date                   --揽收日期
     ,type                        --
     ,truncated_time              --截单时间
     ,is_interrupted              --是否截单
     ,null as plan_collect_arr_time --始发集散规划到达时间
     ,plan_center_arr_time        --始发中心规划到达时间
     ,actual_center_shipment_no   --实际首中心到件任务号     (20220903新增)
     ,customer_code               --客户编号                 (20220903新增）
     ,total_days_t                --T+x时效                  (20220903新增)
     ,center_planned_departure    --中心规划发车时间-1小时   (20220903新增)
     ,is_intime                   --时效是否准点             (20220903新增)
     ,express_type_code           --产品类型编码             (20221103新增)
     ,express_type_name           --产品类型名称             (20221103新增)
     ,last_area_code              --目的区/县编码            (20221113新增)
     ,last_area_name              --目的区/县名称            (20221113新增)
     ,last_city_code              --目的城市编码             (20221113新增)
     ,last_city_name              --目的城市名称             (20221113新增)
     ,last_agent_code             --目的代理区编码           (20221113新增)
     ,last_agent_name             --目的代理区名称           (20221113新增)
     ,first_nodal_send_time       --始发集散发件扫描时间     (20221113新增)
     ,first_nodal_arrival_time    --始发集散到件扫描时间     (20221113新增)
     ,first_center_send_time      --始发转运中心发件扫描时间 (20221113新增)
     ,first_center_arrival_time   --始发转运中心到件扫描时间 (20221113新增)
     ,if_yiqin_late               --是否疫情件               (20221213新增)
     ,fast_total_days             --网点-网点T+X时效         (20221213新增)
     ,first_center_plan_send_time --始发中心规划发车时间     (20230103新增)
     ,customer_name                    --客户名称
     ,first_nodal_agent_code           --实际交件集散点代理区编码
     ,first_nodal_agent_name           --实际交件集散点代理区名称
     ,first_nodal_fran_code            --实际交件集散点加盟商编码
     ,first_nodal_fran_name            --实际交件集散点加盟商名称
     ,actual_center_shipment_shifts    --实际交件支线任务班次 20230420新增
     ,taking_user_code                 --揽收业务员 20230516
     ,taking_user_name                 --揽收业务员 20230516
     ,actual_network_shipment_no       -- 网点到件发件任务单
     ,actual_nodal_shipment_no         -- 集散到件发件任务单
     ,first_network_plan_send_time        --始发网点规划发车时间 20230627
     ,null as first_nodal_plan_send_time  --始发集散规划发车时间 20230627 (网点-中心-网点, 没走集散)
     ,order_mark_en                    -- 订单标签英文
     ,order_mark_zh                    -- 订单标签中文
from (
     select
           *
          ,case when actual_center_arr_time <= center_planned_departure then 1 else 0 end as is_intime --时效是否准点（20220903新增）
          ,row_number() over(partition by waybill_no order by data_num asc) as rn
     from (
          select
                wide.*
               ,route.total_days_t as total_days_t --T+x时效
               ,from_unixtime(unix_timestamp(concat(date_add(wide.scan_date,route.center_span_days + route.in_edge3_stop_days),' ',route.center_plan_send_time))-3600,'yyyy-MM-dd HH:mm:ss') as center_planned_departure --中心规划发车时间-1小时（20220903新增）
               ,concat(date_add(wide.scan_date,route.center_span_days + route.in_edge3_stop_days),' ',route.center_plan_send_time)as first_center_plan_send_time  -- 始发中心规划发车时间
               ,concat(date_add(wide.scan_date,route.reserve_1 + route.in_line_day),' ',route.network_plan_send_time)             as first_network_plan_send_time -- 网点规划发车时间
               ,case when route.is_off = 1 then route.network_lasted_warehousing_time end as truncated_time --截单时间
               ,case when route.is_off = 1 and wide.scan_time > route.network_lasted_warehousing_time then 1 else 0 end as is_interrupted  --是否截单
               ,concat(date_add(wide.scan_date,nodal_span_days+if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)),' ',route.first_nodal_plan_arrival_time) as plan_collect_arr_time --始发集散规划到达时间
               ,case when wide.scan_time < route.first_warehousing_time --揽收时间<最早的最晚入仓时间
                         then concat(date_add(wide.scan_date,if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)+route.center_span_days),' ',route.first_center_plan_arrival_time) --无截单补充揽收跨天
                     when wide.scan_time >=route.first_warehousing_time and wide.scan_time <= route.lead_warehousing_time --揽收时间<下一个最晚入仓时间
                         then concat(date_add(wide.scan_date,if(wide.scan_time > route.last_taking_time and is_off = 0,1,0)+route.center_span_days),' ',route.first_center_plan_arrival_time) --无截单补充揽收跨天
                     when wide.scan_time > route.network_lasted_warehousing_time --揽收时间>最晚的最晚入仓时间
                         then (case when route.is_off = 0 then concat(date_add(wide.scan_date,route.center_span_days+1),' ',route.first_center_plan_arrival_time) --揽收时间 大于 最晚的最晚入仓时间，且无截单，正常取，直接+1天
                                    else from_unixtime(unix_timestamp(concat(date_add(wide.scan_date,route.center_span_days + if(route.branch_in_center_span_days <> 0,route.branch_in_center_span_days,route.in_edge3_stop_days)),' ',route.center_plan_send_time))-3600,'yyyy-MM-dd HH:mm:ss') --有截单，用中心规划发车时间-1小时
                          end)
                     end as plan_center_arr_time --始发中心规划到达时间
               ,case when wide.scan_time > route.network_lasted_warehousing_time
                         then unix_timestamp(concat(date_add(wide.scan_date,1),' ',
                         if(route.last_taking_time >= '00:00:00' and route.last_taking_time <= '06:00:00',route.first_warehousing_time,
                         if(route.last_taking_time = route.network_lasted_warehousing_time,route.last_taking_time,null)))) - unix_timestamp(wide.early_scan_time)
                     when nvl(unix_timestamp(concat(wide.scan_date,' ', route.last_taking_time)),99999999999999999) - unix_timestamp(wide.early_scan_time) >0
                         then nvl(unix_timestamp(concat(wide.scan_date,' ',route.last_taking_time)),99999999999999999) - unix_timestamp(wide.early_scan_time)
                     else 99999999999999999
                     end as data_num
          from (
               select
                    waybill_no                --运单号
                   ,order_source              --订单来源名称
                   ,goods_type_code           --物品类型编码
                   ,goods_type_name           --物品类型名称
                   ,last_network_code         --运单表目的网点编码
                   ,last_network_name         --运单表目的网点名称
                   ,early_scan_time           --最早扫描时间
                   ,early_scan_site_code      --最早扫描时间
                   ,first_nodal_code          --始发集散编码
                   ,first_nodal_name          --始发集散名称
                   ,first_center_code         --始发中心编码
                   ,first_center_name         --始发中心名称
                   ,actual_collect_code       --实际交件集散点编码
                   ,actual_collect_name       --实际交件集散点名称
                   ,actual_center_code        --实际交件中心编码
                   ,actual_center_name        --实际交件中心名称
                   ,collect_proxy_code        --交件代理区编码
                   ,collect_proxy             --交件代理区名称
                   ,actual_send_time          --网点实际发件时间
                   ,plan_collect_code         --规划到达集散编码
                   ,plan_collect_name         --规划到达集散名称
                   ,actual_collect_arr_time   --实际交件集散点时间
                   ,actual_center_arr_time    --实际首中心到车时间
                   ,receive_city_id           --收件城市id
                   ,receive_city              --收件城市
                   ,region_code               --收件管理大区编码
                   ,region_name               --收件管理大区名称
                   ,receive_proxy_code        --收件代理区编码
                   ,receive_proxy             --收件代理区名称
                   ,receive_franchisee_code   --收件加盟商编码
                   ,receive_franchisee        --收件加盟商名称
                   ,receive_network_code      --收件网点编码
                   ,receive_network_name      --收件网点名称
                   ,is_diff_proxy             --是否跨代理区
                   ,scan_time                 --揽收时间
                   ,scan_date                 --揽收日期
                   ,type                      --
                   ,actual_center_shipment_no   --实际首中心到件任务号     (20220903新增)
                   ,customer_code               --客户编号                 (20220903新增）
                   ,express_type_code           --产品类型编码             (20221103新增)
                   ,express_type_name           --产品类型名称             (20221103新增)
                   ,last_area_code              --目的区/县编码            (20221113新增)
                   ,last_area_name              --目的区/县名称            (20221113新增)
                   ,last_city_code              --目的城市编码             (20221113新增)
                   ,last_city_name              --目的城市名称             (20221113新增)
                   ,last_agent_code             --目的代理区编码           (20221113新增)
                   ,last_agent_name             --目的代理区名称           (20221113新增)
                   ,first_nodal_send_time       --始发集散发件扫描时间     (20221113新增)
                   ,first_nodal_arrival_time    --始发集散到件扫描时间     (20221113新增)
                   ,first_center_send_time      --始发转运中心发件扫描时间 (20221113新增)
                   ,first_center_arrival_time   --始发转运中心到件扫描时间 (20221113新增)
                   ,if_yiqin_late               --是否疫情件               (20221213新增)
                   ,fast_total_days             --网点-网点T+X时效         (20221213新增)
                   ,customer_name                    --客户名称
                   ,first_nodal_agent_code           --实际交件集散点代理区编码
                   ,first_nodal_agent_name           --实际交件集散点代理区名称
                   ,first_nodal_fran_code            --实际交件集散点加盟商编码
                   ,first_nodal_fran_name            --实际交件集散点加盟商名称
                   ,actual_center_shipment_shifts    --实际交件支线任务班次 20230420新增
                   ,taking_user_code                 --揽收业务员 20230516
                   ,taking_user_name                 --揽收业务员 20230516
                   ,actual_network_shipment_no       -- 网点到件发件任务单
                   ,actual_nodal_shipment_no         -- 集散到件发件任务单
                   ,order_mark_en                    -- 订单标签英文
                   ,order_mark_zh                    -- 订单标签中文
               from bidefault.dwd_three_route_for_submit
               where plan_center_arr_time is null --网点-中心-网点路由
          ) wide
          left join jms_tmp.dwd_route_for_submit_tmp route
             on wide.early_scan_site_code = route.in_from_code  --按照始发网点、始发中心、目的网点关联兜底路由数据
            and nvl(wide.first_center_code,0) = nvl(route.in_to_code,0)
            and wide.last_network_code = route.out_to_code
            and wide.scan_date = route.update_date
     ) t where data_num >0
) a where a.rn = 1;




-----------------------------------------------最后一步计算未到交接时间的数据
insert overwrite table bidefault.dwd_lasted_route_for_submit
select
      waybill_no                --运单号
     ,order_source              --订单来源名称
     ,goods_type_code           --物品类型编码
     ,goods_type_name           --物品类型名称
     ,last_network_code         --运单表目的网点编码
     ,last_network_name         --运单表目的网点名称
     ,early_scan_time           --最早扫描时间
     ,early_scan_site_code      --最早扫描时间
     ,first_nodal_code          --始发集散编码
     ,first_nodal_name          --始发集散名称
     ,first_center_code         --始发中心编码
     ,first_center_name         --始发中心名称
     ,actual_collect_code       --实际交件集散点编码
     ,actual_collect_name       --实际交件集散点名称
     ,actual_center_code        --实际交件中心编码
     ,actual_center_name        --实际交件中心名称
     ,collect_proxy_code        --交件代理区编码
     ,collect_proxy             --交件代理区名称
     ,actual_send_time          --网点实际发件时间
     ,plan_collect_code         --规划到达集散编码
     ,plan_collect_name         --规划到达集散名称
     ,actual_collect_arr_time   --实际交件集散点时间
     ,actual_center_arr_time    --实际首中心到车时间
     ,receive_city_id           --收件城市id
     ,receive_city              --收件城市
     ,region_code               --收件管理大区编码
     ,region_name               --收件管理大区名称
     ,receive_proxy_code        --收件代理区编码
     ,receive_proxy             --收件代理区名称
     ,receive_franchisee_code   --收件加盟商编码
     ,receive_franchisee        --收件加盟商名称
     ,receive_network_code      --收件网点编码
     ,receive_network_name      --收件网点名称
     ,is_diff_proxy             --是否跨代理区
     ,scan_time                 --揽收时间
     ,scan_date                 --揽收日期
     ,case when plan_center_arr_time >= concat(date_add(to_date(early_scan_time),2),' 07:00:00') then 6 else 0 end as type --类型 未到交接时间
     ,truncated_time            --截单时间
     ,is_interrupted            --是否截单
     ,plan_collect_arr_time     --始发集散规划到达时间
     ,plan_center_arr_time      --始发中心规划到达时间
     ,actual_center_shipment_no --实际首中心到件任务号     (20220903新增)
     ,customer_code             --客户编号                 (20220903新增)
     ,total_days_t              --T+x时效                  (20220903新增)
     ,center_planned_departure  --中心规划发车时间-1小时   (20220903新增)
     ,is_intime                 --时效是否准点             (20220903新增)
     ,express_type_code         --产品类型编码             (20221103新增)
     ,express_type_name         --产品类型名称             (20221103新增)
     ,last_area_code            --目的区/县编码            (20221113新增)
     ,last_area_name            --目的区/县名称            (20221113新增)
     ,last_city_code            --目的城市编码             (20221113新增)
     ,last_city_name            --目的城市名称             (20221113新增)
     ,last_agent_code           --目的代理区编码           (20221113新增)
     ,last_agent_name           --目的代理区名称           (20221113新增)
     ,first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
     ,first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
     ,first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
     ,first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
     ,if_yiqin_late             --是否疫情件               (20221213新增)
     ,fast_total_days           --网点-网点T+X时效         (20221213新增)
     ,first_center_plan_send_time --始发中心规划发车时间     (20230103新增)
     ,customer_name                    --客户名称
     ,first_nodal_agent_code           --实际交件集散点代理区编码
     ,first_nodal_agent_name           --实际交件集散点代理区名称
     ,first_nodal_fran_code            --实际交件集散点加盟商编码
     ,first_nodal_fran_name            --实际交件集散点加盟商名称
     ,actual_center_shipment_shifts    --实际交件支线任务班次 20230420新增
     ,taking_user_code                 --揽收业务员 20230516
     ,taking_user_name                 --揽收业务员 20230516
     ,actual_network_shipment_no       --网点到件发件任务单
     ,actual_nodal_shipment_no         --集散到件发件任务单
     ,first_network_plan_send_time     --始发网点规划发车时间
     ,first_nodal_plan_send_time       --始发集散规划发车时间
     ,order_mark_en                    -- 订单标签英文
     ,order_mark_zh                    -- 订单标签中文
from (
    select * from bidefault.dwd_collect_route_for_submit where type = 0 --网点-集散-应交件
    union all
    select * from bidefault.dwd_three_route_for_submit where type = 0 and plan_center_arr_time is not null --网点-集散-中心应交件
    union all
    select * from bidefault.dwd_four_route_for_submit where type = 0 --网点-中心应交件

);



-----------------------------最后结果判责
insert overwrite table bidefault.dwd_result_route_for_submit
select
      waybill_no
     ,order_source
     ,goods_type_code
     ,goods_type_name
     ,last_network_code
     ,last_network_name
     ,early_scan_time
     ,early_scan_site_code
     ,first_nodal_code
     ,first_nodal_name
     ,first_center_code
     ,first_center_name
     ,actual_collect_code
     ,actual_collect_name
     ,actual_center_code
     ,actual_center_name
     ,collect_proxy_code
     ,collect_proxy
     ,actual_send_time
     ,plan_collect_code
     ,plan_collect_name
     ,actual_collect_arr_time
     ,actual_center_arr_time
     ,receive_city_id
     ,receive_city
     ,region_code
     ,region_name
     ,receive_proxy_code
     ,receive_proxy
     ,receive_franchisee_code
     ,receive_franchisee
     ,receive_network_code
     ,receive_network_name
     ,is_diff_proxy
     ,type
     ,is_interrupted
     ,truncated_time
     ,plan_collect_arr_time
     ,plan_center_arr_time          as plan_center_arr_time  --规划首中心到车时间
     ,plan_center_arr_time          as hand_over_latest      --交接最晚到车时间
     ,to_date(early_scan_time)      as taking_date           --揽收日期
     ,to_date(plan_center_arr_time) as plan_arr_date         --规划到件日期
     ,is_arr_center_intime
     ,is_arr_collect_intime
     ,hand_over_intime as is_over
     ,case when hand_over_intime in (0,2) then 0 else 1 end as hand_over_intime
     ,case when hand_over_intime = 0 then early_scan_site_code
           when hand_over_intime = 2 then actual_collect_code end as hand_over_responsible_code --交接不准点责任所属机构编码
     ,case when hand_over_intime = 0 then receive_network_name
           when hand_over_intime = 2 then actual_collect_name end as hand_over_responsible      --交接不准点责任所属机构名称
     ,actual_center_shipment_no --实际首中心到件任务号     (20220903新增)
     ,customer_code             --客户编号                 (20220903新增)
     ,total_days_t              --T+x时效                  (20220903新增)
     ,center_planned_departure  --中心规划发车时间-1小时   (20220903新增)
     ,is_intime                 --时效是否准点             (20220903新增)
     ,express_type_code         --产品类型编码             (20221103新增)
     ,express_type_name         --产品类型名称             (20221103新增)
     ,last_area_code            --目的区/县编码            (20221113新增)
     ,last_area_name            --目的区/县名称            (20221113新增)
     ,last_city_code            --目的城市编码             (20221113新增)
     ,last_city_name            --目的城市名称             (20221113新增)
     ,last_agent_code           --目的代理区编码           (20221113新增)
     ,last_agent_name           --目的代理区名称           (20221113新增)
     ,first_nodal_send_time     --始发集散发件扫描时间     (20221113新增)
     ,first_nodal_arrival_time  --始发集散到件扫描时间     (20221113新增)
     ,first_center_send_time    --始发转运中心发件扫描时间 (20221113新增)
     ,first_center_arrival_time --始发转运中心到件扫描时间 (20221113新增)
     ,if_yiqin_late             --是否疫情件               (20221213新增)
     ,fast_total_days           --网点-网点T+X时效         (20221213新增)
     ,case when substr(total_days_t,2,4) > substr(fast_total_days,2,4) then 1 else 0 end if_route_late  --路由晚点(20221213新增)
     ,case when hand_over_intime <> 1 and first_center_send_time <= first_center_plan_send_time then 1
           when hand_over_intime <> 1 and nvl(first_center_send_time,'2099-12-31') >  nvl(first_center_plan_send_time,'0000-00-00') then 0 end as if_center_send_timely --是否交件晚点及时发出1:是,0:否
     ,customer_name                    --客户名称
     ,first_nodal_agent_code           --实际交件集散点代理区编码
     ,first_nodal_agent_name           --实际交件集散点代理区名称
     ,first_nodal_fran_code            --实际交件集散点加盟商编码
     ,first_nodal_fran_name            --实际交件集散点加盟商名称
     ,actual_center_shipment_shifts    --实际交件支线任务班次 20230420新增
     ,taking_user_code                 --揽收业务员 20230516
     ,taking_user_name                 --揽收业务员 20230516
     ,actual_network_shipment_no       --网点到件发件任务单
     ,actual_nodal_shipment_no         --集散到件发件任务单
     ,first_network_plan_send_time     --始发网点规划发车时间
     ,first_nodal_plan_send_time       --始发集散规划发车时间
     ,null   as hand_over_duty_flag  -- 不准点责任标识   2网点发车晚,3网点到集散运输晚,4网点到中心运输晚,5集散发车晚,6集散到中心运输晚
     ,order_mark_en                    -- 订单标签英文
     ,order_mark_zh                    -- 订单标签中文
from (
     select
           *
          ,case when actual_collect_arr_time <= plan_collect_arr_time then 1
                when actual_collect_arr_time >  plan_collect_arr_time then 0
                 end as is_arr_collect_intime --到达集散是否准点
          ,case when actual_collect_arr_time <= plan_center_arr_time then 1
                when actual_collect_arr_time >  plan_center_arr_time then 0
                 end as is_arr_center_intime  --到达中心是否准点
          ,case when nvl(plan_center_arr_time,'0000-00-00 00:00:00') < concat(date_add(to_date(early_scan_time),2),' 07:00:00') then  --始发中心规划发车时间<揽收日期+2天7点
                    (case when early_scan_site_code is not null and first_nodal_code is not null and first_center_code is not null then --网点-集散-中心
                              (case when actual_center_arr_time <= plan_center_arr_time then 1
                                    when actual_collect_arr_time <= plan_collect_arr_time then 2
                                    else 0 end
                                  )
                          when early_scan_site_code is not null and first_nodal_code is null and first_center_code is not null then --网点-中心
                              (case when actual_center_arr_time <= plan_center_arr_time then 1 else 0 end)
                          when early_scan_site_code is not null and first_nodal_code is not null and first_center_code is null then --网点-集散
                              (case when actual_collect_arr_time <= plan_collect_arr_time then 2 else 0 end)
                          when early_scan_site_code is not null and first_nodal_code is null and first_center_code is null then 0 --网点-
                          else 0 end
                        ) end as hand_over_intime --交接是否准点  1准点 2集散责任 0网点责任
     from bidefault.dwd_lasted_route_for_submit
     where type = 0
) a;